#!/usr/bin/perl
#
# PSynGear V4 
#
# Last Update 01/12/2006
# Perl GUI to synch Philips MP3 Players
# For Use With Philips HDD0xx on Linux and Window's Platform
#
# USAGE:
#     >> load the program './PSynGear' or 'perl PSynGear' in a terminal
#     >> Enter path to GoGear root in text box and hit 'Set Path'
#     >> Add/Remove Files then click 'Done' when finished
#     >> May need to edit $types if you use different audio files
#
# TODO: 
#     >> Find a fix so the window doesn't freeze during adding/removing of tracks, maybe spawn a new thread?
#        Thus fixing the progress bar which is currently disabled
#     >> Comment the code
#
# CHANGELOG:
#  V4:
#     >> Fixed the synch process, no longer does it read the entire device instead only modifys the DB when 
#        a track is removed or added, which speeds up the whole process ten fold
#     >> Hopefully fixed the escaping of ' characters in SQL queries
#     >> Added changelog
#  V3:
#     >> Reworked Synch process to fix Genre's
#     >> Code rewrite to make os cross-compatible
#     >> Fixed the scrolling Bug
#  V2:
#     >> Added GUI
#     >> Fixed the bug in the ordering of items in list boxes
#  V1:
#     >> Intial release    
#

	use strict;
	use warnings;

	use Tk 804;
	use Tk::ProgressBar;
	use MP3::Info ();
	use DBI;
	use File::Spec::Functions qw(abs2rel catdir catfile splitdir);
	use File::Copy;

	my $types = [['MP3 Files', '.mp3'], ['Windows Media', '.wma']];

	my ($main,
		$gogear_mount,						
		$gogear_base,
		$gogear_db,
		$local_db,
		%widget,
		$progress,
		@mp3s,
		$dbh,
		@track_l,
		@artist_l,
		@album_l);

	$main = MainWindow->new;
	$main->title("PSynGear");

	$widget{'Button1'} = $main->Button(
   		-command => 'main::openfile',
   		-text    => 'Add Track[s]',
  		)->grid(
   		-row    => 0,
   		-column => 0);

	$widget{'Button2'} = $main->Button(
   		-command => 'main::removefile',
  		-text => 'Remove Track[s]',
 		)->grid(
   		-row    => 0,
   		-column => 2);

	$widget{'Frame2'} = $main->Frame()->grid(
   		-row    => 3,
   		-column => 1);

	$widget{'Frame3'} = $main->Frame()->grid(
   		-row    => 3,
   		-column => 2);

	$widget{'Button7'} = $widget{'Frame3'}->Button(
   		-text => 'Done',
   		-command => 'main::done',
  		)->grid(
   		-row    => 0,
   		-column => 0);

	$widget{'Button3'} = $widget{'Frame2'}->Button(
   		-text => 'Set Path',
   		-command => 'main::setpath',
  		)->grid(
   		-row    => 0,
   		-column => 0);

	$widget{'track'} = $main->Listbox(
   		-exportselection => 1,
   		-height          => 30,
   		-selectmode      => 'extended',
  		)->grid(
   		-row    => 2,
   		-column => 0);

	$widget{'artist'} = $main->Listbox(
   		-exportselection => 1,
   		-height          => 30,
   		-selectmode      => 'extended',
  		)->grid(
   		-row    => 2,
   		-column => 1);

	$widget{'album'} = $main->Listbox(
   		-exportselection => 1,
   		-height => 30,
   		-selectmode      => 'extended',
  		)->grid(
   		-row    => 2,
   		-column => 2);

	$widget{'ProgressBar1'} = $main->ProgressBar(
   		-gap => 0,
		-variable => \$progress,
   		)->grid(
   		-row    => 0,
   		-column => 1);

	$widget{'Label1'} = $main->Label(
   		-text => 'Track',
  		)->grid(
   		-row    => 1,
   		-column => 0);

	$widget{'Label2'} = $main->Label(
   		-text => 'Artist',
  		)->grid(
   		-row    => 1,
   		-column => 1);

	$widget{'Label3'} = $main->Label(
  		-text => 'Album',
  		)->grid(
   		-row    => 1,
   		-column => 2);

	$widget{'Entry2'} = $main->Entry()->grid(
   		-row    => 3,
   		-column => 0);

	$widget{'Frame1'} = $main->Frame()->grid(
   		-row    => 2,
   		-column => 4);

	$widget{'up'} = $widget{'Frame1'}->Button(
		-command => 'main::scrollup',
   		-height => 5,
   		-text   => 'Up',
   		-width  => 4,
  		)->grid(
   		-row    => 0,
   		-column => 0);


	$widget{'down'} = $widget{'Frame1'}->Button(
		-command => 'main::scrolldown',
   		-height => 5,
   		-text   => 'Down',
   		-width  => 4,
  		)->grid(
   		-row    => 1,
   		-column => 0);



	$widget{'track'}->insert('end', @track_l );
	$widget{'artist'}->insert('end', @artist_l );
	$widget{'album'}->insert('end', @album_l );

	tie @track_l, "Tk::Listbox", $widget{'track'};
	tie @album_l, "Tk::Listbox", $widget{'album'};
	tie @artist_l, "Tk::Listbox", $widget{'artist'};

	MainLoop;

	sub setpath {

		my $path = $widget{'Entry2'}->get;

		$widget{'Entry2'}->delete(0, length($path));
		$gogear_mount = $path;						
		$gogear_base = catdir($gogear_mount, qw(_system media audio));
		$gogear_db = catfile($gogear_base, 'MyDb');
		$local_db = 'MyDb';

		copy($gogear_db, $local_db) || do { $widget{'Entry2'}->insert(0, "Incorrect Path"); return; };
		do { $widget{'Entry2'}->insert(0, "Incorrect Path"); return; } unless -d $gogear_base;
		$dbh = DBI->connect("dbi:SQLite2:$local_db", {RaiseError =>1});

		$widget{'Entry2'}->delete(0, length($path));
		$widget{'Entry2'}->insert(0, "Path Set");
		populate();

	}

	sub openfile {

		my $ret = $main->getOpenFile(-multiple => 9999999, -filetypes => $types);
 		foreach my $path (@$ret) {

			(my $v, my $d, my $file) = File::Spec->splitpath($path);
			my $gogear_f = catfile($gogear_base, $file);
			copy($path, $gogear_f)  || do {my $text = $widget{'Entry2'}->get; $widget{'Entry2'}->delete(0, length($text)); $widget{'Entry2'}->insert(0, "Set Path!"); return; };

			my $info = MP3::Info->new($path);
			push(@track_l, $info->title);
			push(@artist_l, $info->artist);
			push(@album_l, $info->album);			   
 
    			eval {$dbh->do("insert into songTable (cSongTitle, iArtistId, iAlbumId, ITrackNr, iTrackLength, cFileName, iDirId, iGenreId, iBitRate, iFileSize, iMediaType) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", undef, $info->title, search('artist', $info->artist), search('album', $info->album), $info->tracknum, $info->secs, $file, 4, search('genre',  $info->genre), $info->bitrate, $info->size, 1);};       


 		}
		$widget{'track'}->yviewMoveto(1);
		$widget{'artist'}->yviewMoveto(1);
		$widget{'album'}->yviewMoveto(1);
	}

	sub removefile {

		my @track = $widget{'track'}->curselection;
		my @artist = $widget{'artist'}->curselection;
		my @album = $widget{'album'}->curselection;

		if (@track) {

			foreach (@track) {
				
				my $track = $track_l[$_];
				$track =~ s/'/''/;
				my $path = $dbh->selectall_arrayref("SELECT * FROM songTable WHERE cSongTitle =  '$track'", { Slice => {} });
				foreach my $path (@$path) {

					my $full_path = catfile($gogear_base, $path->{cFileName});
					unlink($full_path);
					$dbh->do("DELETE FROM songTable WHERE cSongTitle = '$track'");

					my $album = $dbh->selectall_arrayref("SELECT * FROM songTable WHERE iAlbumId = '$path->{iAlbumId}'", { Slice => {} });
					my $al = 0;

					foreach (@$album) {
						$al++;
					}

					if ($al == 0) {
						$dbh->do("DELETE FROM albumTable WHERE iAlbumId = '$path->{iAlbumId}'");
					}

					my $artist = $dbh->selectall_arrayref("SELECT * FROM songTable WHERE iArtistId = '$path->{iArtistId}'", { Slice => {} });
					my $ar = 0;

					foreach (@$artist) {
						$ar++;
					}
				
					if ($ar == 0) {
						$dbh->do("DELETE FROM artistTable WHERE iArtistId = '$path->{iArtistId}'");
					}
				}
			}

			my @remove = reverse sort { $a <=> $b } @track;
			delete @track_l[@remove];
			delete @artist_l[@remove];
			delete @album_l[@remove];
		} 
		elsif (@artist) {

			foreach (@artist) {

				my $track = $track_l[$_];
				$track =~ s/'/''/;
				my $path = $dbh->selectall_arrayref("SELECT * FROM songTable WHERE cSongTitle =  '$track'", { Slice => {} });
				foreach my $path (@$path) {

					my $full_path = catfile($gogear_base, $path->{cFileName});
					unlink($full_path);
					$dbh->do("DELETE FROM songTable WHERE cSongTitle = '$track'");

					my $album = $dbh->selectall_arrayref("SELECT * FROM songTable WHERE iAlbumId = '$path->{iAlbumId}'", { Slice => {} });
					my $al = 0;

					foreach (@$album) {
						$al++;
					}

					if ($al == 0) {
						$dbh->do("DELETE FROM albumTable WHERE iAlbumId = '$path->{iAlbumId}'");
					}

					my $artist = $dbh->selectall_arrayref("SELECT * FROM songTable WHERE iArtistId = '$path->{iArtistId}'", { Slice => {} });
					my $ar = 0;

					foreach (@$artist) {
						$ar++;
					}
				
					if ($ar == 0) {
						$dbh->do("DELETE FROM artistTable WHERE iArtistId = '$path->{iArtistId}'");
					}
				}
			}

			my @remove = reverse sort { $a <=> $b } @artist;
    			delete @track_l[@remove];
			delete @artist_l[@remove];
			delete @album_l[@remove];
		}
		elsif (@album) {

			foreach (@album) {

				my $track = $track_l[$_];
				$track =~ s/'/''/;
				my $path = $dbh->selectall_arrayref("SELECT * FROM songTable WHERE cSongTitle =  '$track'", { Slice => {} });
				foreach my $path (@$path) {

					my $full_path = catfile($gogear_base, $path->{cFileName});
					unlink($full_path);
					$dbh->do("DELETE FROM songTable WHERE cSongTitle = '$track'");

					my $album = $dbh->selectall_arrayref("SELECT * FROM songTable WHERE iAlbumId = '$path->{iAlbumId}'", { Slice => {} });
					my $al = 0;

					foreach (@$album) {
						$al++;
					}

					if ($al == 0) {
						$dbh->do("DELETE FROM albumTable WHERE iAlbumId = '$path->{iAlbumId}'");
					}

					my $artist = $dbh->selectall_arrayref("SELECT * FROM songTable WHERE iArtistId = '$path->{iArtistId}'", { Slice => {} });
					my $ar = 0;

					foreach (@$artist) {
						$ar++;
					}
				
					if ($ar == 0) {
						$dbh->do("DELETE FROM artistTable WHERE iArtistId = '$path->{iArtistId}'");
					}
				}
			}


			my @remove = reverse sort { $a <=> $b } @album;
    			delete @track_l[@remove];
			delete @artist_l[@remove];
			delete @album_l[@remove];
		}
	}

	sub populate {

		my $results = $dbh->selectall_arrayref("SELECT * FROM songTable ORDER BY iSongId", { Slice => {} });

		foreach my $results (@$results) {

			push(@track_l,  $results->{cSongTitle});
		
      			my $artist = $dbh->selectall_arrayref("SELECT * FROM artistTable WHERE iArtistId =  '$results->{iArtistId}'", { Slice => {} });
			my $album = $dbh->selectall_arrayref("SELECT * FROM albumTable WHERE iAlbumId =  '$results->{iAlbumId}'", { Slice => {} });
			foreach my $artist (@$artist) {

				push(@artist_l, $artist->{cArtistName});
			}
			foreach my $album (@$album) {

				push(@album_l, $album->{cAlbumTitle});
			}		
  		}

		$widget{'track'}->yviewMoveto(1);
		$widget{'artist'}->yviewMoveto(1);
		$widget{'album'}->yviewMoveto(1);
		
	}

	sub search {
		
		my $type = shift;
		my $data = shift;
		my ($table, $id, $text);
		if ($type =~ /album/i) {

			$table = "albumTable";
			$id = "iAlbumId";
			$text = "cAlbumTitle";
		}
		elsif ($type =~ /artist/i) {

			$table = "artistTable";
			$id = "iArtistId";
			$text = "cArtistName";
		}
		elsif ($type =~ /genre/i) {
		
			$table = "genreTable";
			$id = "iGenreId";
			$text = "cGenreName";
		}
  
    		if (my $results = $dbh->selectrow_array("select $id from $table where $text = ?", undef, $data)) {

        		return $results;
    		} else {

        		$dbh->do("insert into $table ($text) values (?)", undef, $data);
        		return $dbh->func('last_insert_rowid');
    		}
	}

	sub scrollup {

		$widget{'track'}->yviewScroll('-1', 'pages');
		$widget{'album'}->yviewScroll('-1', 'pages');
		$widget{'artist'}->yviewScroll('-1', 'pages');
	}

	sub scrolldown {

		$widget{'track'}->yviewScroll('1', 'pages');
		$widget{'album'}->yviewScroll('1', 'pages');
		$widget{'artist'}->yviewScroll('1', 'pages');
	}
		
	sub done {

		copy($local_db, $gogear_db) || do { my $path = $widget{'Entry2'}->get; $widget{'Entry2'}->delete(0, length($path)); $widget{'Entry2'}->insert(0, "Transfer Failed"); return; };
		$dbh->disconnect;
		unlink $local_db;
		do { my $path = $widget{'Entry2'}->get; $widget{'Entry2'}->delete(0, length($path)); $widget{'Entry2'}->insert(0, "Done"); return; };
	}
